package com.fly.demo.service;

import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.time.DateFormatUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import lombok.extern.slf4j.Slf4j;

@Slf4j
@Service
public class UserService
{
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    /**
     * 根据条件查询数据<br>
     * (@Cacheable(sync=true) does not support unless attribute)
     * 
     * @param name
     * @param startTime
     * @param endTime
     * @return
     */
    @Cacheable(value = "user", unless = "#result.size() == 0")
    public List<Map<String, Object>> query(String name, Date startTime, Date endTime)
    {
        // 忽略时分秒比较日期
        // SELECT * FROM user WHERE DATE(create_time) = '2024-07-10' ;
        // SELECT * FROM user WHERE DATE(create_time) = DATE('2024-07-10');
        // SELECT * FROM user WHERE DATE(create_time) BETWEEN '2024-07-10' AND '2024-07-31';
        
        log.info("#### query name: {}, time: {} -> {} ####", name, startTime, endTime);
        List<Map<String, Object>> list = jdbcTemplate.queryForList(
            "SELECT username, DATE_FORMAT(create_time, '%Y-%m-%d %H:%i:%s') AS date FROM user WHERE username LIKE CONCAT('%', ?, '%') AND create_time >= STR_TO_DATE (?, '%Y-%m-%d %H:%i:%s') AND create_time <= STR_TO_DATE (?, '%Y-%m-%d %H:%i:%s')",
            name,
            DateFormatUtils.format(startTime, "yyyy-MM-dd HH:mm:ss"),
            DateFormatUtils.format(endTime, "yyyy-MM-dd HH:mm:ss"));
        // SELECT username, DATE_FORMAT(create_time, '%Y-%m-%d %H:%i:%s') AS date FROM user
        // WHERE username LIKE CONCAT('%', ?, '%')
        // AND STR_TO_DATE (create_time, '%Y-%m-%d %H:%i:%s') >= STR_TO_DATE (?, '%Y-%m-%d %H:%i:%s')
        // AND STR_TO_DATE (create_time, '%Y-%m-%d %H:%i:%s') <= STR_TO_DATE (?, '%Y-%m-%d %H:%i:%s')
        return list;
    }
}
